[Mysql] 测试mysql where条件执行顺序对sql查询效率的影响


本文总阅读量次

1、创建基表测试数据

1
2
3
4
5
6
7
8
CREATE TABLE `test1` (
  `id` int(7) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4;

insert into test1(name,age) values('lucy',10),('bobo',18),('david',20),('tom',21),('dobu',22),('dali',12);

2、创建中间表

1
2
3
4
5
6
 CREATE TABLE `testfororder` (
  `id` int(7) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14699990 DEFAULT CHARSET=utf8mb4;

创建存储过程对中间表插入数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
drop procedure if exists test;
delimiter //
create procedure test()
 begin
  declare i int;
  set i=0;
  while i<300000 do
   insert into testfororder(newid,name,age) select concat(i,name),FLOOR(18 + (RAND() * 12)) from test1;
   set i=i+1;
  end while;
 end//
 delimiter ;
调用存储过程：
call test();

3、创建测试表：

1
2
3
4
5
6
7
8
9
CREATE TABLE `sequence` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `newid` int(10) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=89179437 DEFAULT CHARSET=utf8mb4;

insert into sequence(newid,name,age) select * from testfororder;

数据量根据自己需求可以多次导入

4、查看sequence测试表数据：

1
2
3
4
5
6
7
select count(*) from sequence;
+----------+
| count(*) |
+----------+
| 12600000 |
+----------+
1 row in set (2.83 sec)

age条件筛选数据量

1
2
3
4
5
6
7
select count(*) from sequence where age in (26,19,22,20,28,29,25);
+----------+
| count(*) |
+----------+
|  7341992 |
+----------+
1 row in set (3.82 sec)

newid条件筛选数量

1
2
3
4
5
6
7
select count(*) from sequence where newid in (116670,116677,116684,116691,116698,116705,116719,116726);
+----------+
| count(*) |
+----------+
|       56 |
+----------+
1 row in set (3.28 sec)

5、where条件(age,newid)在没有创建索引的情况下：

age在前，newid在后，查看执行计划：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
explain select * from sequence where age in (26,19,22,20,28,29,25) and newid in (116670,116677,116684,116691,116698,116705,116719,116726);
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | sequence | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 12292293 |    25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

select * from sequence where age in (26,19,22,20,28,29,25) and newid in (116670,116677,116684,116691,116698,116705,116719,116726);
+----------+--------+-----------+------+
| id       | newid  | name      | age  |
+----------+--------+-----------+------+
|   100003 | 116670 | 2381lucy  |   26 |
...
| 76679487 | 116719 | 2382lucy  |   29 |
+----------+--------+-----------+------+
49 rows in set (5.36 sec)

newid在前，age在后：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
explain select * from sequence where  newid in (116670,116677,116684,116691,116698,116705,116719,116726) and age in (26,19,22,20,28,29,25);
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | sequence | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 12292293 |    25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

select * from sequence where  newid in (116670,116677,116684,116691,116698,116705,116719,116726) and age in (26,19,22,20,28,29,25);
+----------+--------+-----------+------+
| id       | newid  | name      | age  |
+----------+--------+-----------+------+
|   100003 | 116670 | 2381lucy  |   26 |
...
| 76679487 | 116719 | 2382lucy  |   29 |
+----------+--------+-----------+------+
49 rows in set (4.47 sec)

没有任何索引的情况下，where后面跟的条件从左到右，返回数据越小的条件在前面，效率会优于返回数据较大的条件在前面。

6、where条件(age)在没有创建索引，newid创建索引的情况下：

创建newid的索引：

1
create index newid_ind on sequence(newid);

age在前，newid在后：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
explain select * from sequence where age in (26,19,22,20,28,29,25) and newid in (116670,116677,116684,116691,116698,116705,116719,116726);
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | sequence | NULL       | range | newid_ind     | newid_ind | 4       | NULL |   56 |    50.00 | Using index condition; Using where |
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.01 sec)

select * from sequence where age in (26,19,22,20,28,29,25) and newid in (116670,116677,116684,116691,116698,116705,116719,116726);
+----------+--------+-----------+------+
| id       | newid  | name      | age  |
+----------+--------+-----------+------+
|   100003 | 116670 | 2381lucy  |   26 |
...
| 76679487 | 116719 | 2382lucy  |   29 |
+----------+--------+-----------+------+
49 rows in set (0.01 sec)

newid在前，age在后：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
explain select * from sequence where  newid in (116670,116677,116684,116691,116698,116705,116719,116726) and age in (26,19,22,20,28,29,25);
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | sequence | NULL       | range | newid_ind     | newid_ind | 4       | NULL |   56 |    50.00 | Using index condition; Using where |
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.01 sec)

select * from sequence where  newid in (116670,116677,116684,116691,116698,116705,116719,116726) and age in (26,19,22,20,28,29,25);
+----------+--------+-----------+------+
| id       | newid  | name      | age  |
+----------+--------+-----------+------+
|   100003 | 116670 | 2381lucy  |   26 |
...
| 76679487 | 116719 | 2382lucy  |   29 |
+----------+--------+-----------+------+
49 rows in set (0.00 sec)

在newid创建索引的情况下，where后面的查询条件前后顺序对查询效率影响不大。

7、where条件(newid)在没有创建索引，age创建索引的情况下：

删除newid的索引：

1
drop index newid_ind on sequence;

创建age的索引：

1
create index age_ind on sequence(age);

age在前，newid在后：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
explain select * from sequence where age in (26,19,22,20,28,29,25) and newid in (116670,116677,116684,116691,116698,116705,116719,116726);
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | sequence | NULL       | ALL  | age_ind       | NULL | NULL    | NULL | 12292293 |    50.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

select * from sequence where age in (26,19,22,20,28,29,25) and newid in (116670,116677,116684,116691,116698,116705,116719,116726);
+----------+--------+-----------+------+
| id       | newid  | name      | age  |
+----------+--------+-----------+------+
|   100003 | 116670 | 2381lucy  |   26 |
...
| 76679487 | 116719 | 2382lucy  |   29 |
+----------+--------+-----------+------+
49 rows in set (4.97 sec)

newid在前，age在后：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
explain select * from sequence where  newid in (116670,116677,116684,116691,116698,116705,116719,116726) and age in (26,19,22,20,28,29,25);
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | sequence | NULL       | ALL  | age_ind       | NULL | NULL    | NULL | 12292293 |    50.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

select * from sequence where  newid in (116670,116677,116684,116691,116698,116705,116719,116726) and age in (26,19,22,20,28,29,25);
+----------+--------+-----------+------+
| id       | newid  | name      | age  |
+----------+--------+-----------+------+
|   100003 | 116670 | 2381lucy  |   26 |
...
| 76679487 | 116719 | 2382lucy  |   29 |
+----------+--------+-----------+------+
49 rows in set (4.82 sec)

创建age索引之后，执行计划会默认使用索引执行，但是因为age的筛选出来的数据量比较大，使用索引也不是特别理想。

8、where条件(newid,age)创建联合索引的情况下：

删除age索引，创建newid，age的联合索引：

1
2
drop index age_ind on sequence;
create index newid_age_ind on sequence(newid,age);

age在前，newid在后：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
explain select * from sequence where age in (26,19,22,20,28,29,25) and newid in (116670,116677,116684,116691,116698,116705,116719,116726);
+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | sequence | NULL       | range | newid_age_ind | newid_age_ind | 9       | NULL |   98 |   100.00 | Using index condition |
+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+

 select * from sequence where age in (26,19,22,20,28,29,25) and newid in (116670,116677,116684,116691,116698,116705,116719,116726);
+----------+--------+-----------+------+
| id       | newid  | name      | age  |
+----------+--------+-----------+------+
|   100003 | 116670 | 2381lucy  |   26 |
...
| 76679487 | 116719 | 2382lucy  |   29 |
+----------+--------+-----------+------+
49 rows in set (0.00 sec)

newid在前，age在后：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
explain select * from sequence where  newid in (116670,116677,116684,116691,116698,116705,116719,116726) and age in (26,19,22,20,28,29,25);
+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | sequence | NULL       | range | newid_age_ind | newid_age_ind | 9       | NULL |   98 |   100.00 | Using index condition |
+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

select * from sequence where  newid in (116670,116677,116684,116691,116698,116705,116719,116726) and age in (26,19,22,20,28,29,25);
+----------+--------+-----------+------+
| id       | newid  | name      | age  |
+----------+--------+-----------+------+
|   100003 | 116670 | 2381lucy  |   26 |
...
| 76679487 | 116719 | 2382lucy  |   29 |
+----------+--------+-----------+------+
49 rows in set (0.01 sec)

联合索引存在的情况下，where后面的查询条件前后顺序对查询效率影响不大，但是创建了联合索引之后要注意查询条件问题，如果是以（newid，age）的顺序创建的联合索引，如果where查询条件后面有newid会使用联合索引，但是如果where查询条件后面之后age则不会引用联合索引：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
explain select * from sequence where  newid in (116670,116677,116684,116691,116698,116705,116719,116726);
+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | sequence | NULL       | range | newid_age_ind | newid_age_ind | 4       | NULL |   56 |   100.00 | Using index condition |
+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

explain select * from sequence where age in (26,19,22,20,28,29,25);
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | sequence | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 12292293 |    50.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
mysql
目录
  1. 1. 1、创建基表测试数据
  2. 2. 2、创建中间表
  3. 3. 3、创建测试表：
  4. 4. 4、查看sequence测试表数据：
  5. 5. 5、where条件(age,newid)在没有创建索引的情况下：
  6. 6. 6、where条件(age)在没有创建索引，newid创建索引的情况下：
  7. 7. 7、where条件(newid)在没有创建索引，age创建索引的情况下：
  8. 8. 8、where条件(newid,age)创建联合索引的情况下：

Proudly powered by Hexo and Theme by Lap
本站访客数人次
© 2020 zeven0707's blog